﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using ET.DBCommon.Model;
using MySql.Data.MySqlClient;

namespace ET.DBCommon.DAL
{
    public class ExamresultsTestQuestionTaskDAL
    {
        internal static ObservableCollection<ExamresultsTestQuestionTaskModel> GetQuestionTaskByExamResultsTestQuestion(ExamresultsTestQuestionModel examResultsTestQuestion)
        {
            string sql = "SELECT * FROM examresultstestquestiontask where ExamResultsTestQuestionID = " + examResultsTestQuestion.ID;
            foreach (var id in examResultsTestQuestion.AssistantQuestions.Split(','))
            {
                if (!string.IsNullOrWhiteSpace(id))
                    sql += " or ExamResultsTestQuestionID =" + id;
            }
            sql += " order by UIOrder";
            DataSet ds = DbHelper.ExecuteDataset(sql);

            ObservableCollection<ExamresultsTestQuestionTaskModel> list = new ObservableCollection<ExamresultsTestQuestionTaskModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(GetExamresultsTestQuestionTaskModel(dt.Rows[i], examResultsTestQuestion.ID));
                }
            }
            return list;
        }

        internal static DataSet GetQuestionTask(int examResultsID)
        {
            string sql = "SELECT a.*,b.Tooltips,b.isother,c.`Describe` FROM examresultstestquestiontask a inner join testquestiontask b on a.taskid=b.testquestiontaskid" +
                " inner join taskitem c on b.taskid=c.taskid inner join examresultstestquestion d on a.ExamResultsTestQuestionID=d.ExamResultsTestQuestionID" +
                " where d.ExamResultsID = " + examResultsID + " order by b.`Order`";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            return ds;
        }

        public static bool UpdateExamresultsTestQuestionTask(ExamresultsTestQuestionTaskModel examresultsTestQuestionTask)
        {
            //string sql = "update examresultstestquestiontask set BeginTime=@BeginTime,EndTime=@EndTime,TimeScore=@TimeScore,SubjectiveScore=@SubjectiveScore,ResultScore=@ResultScore,OperationScore=@OperationScore  WHERE ExamResultsTestQuestionTaskID = @ID";
            string sql = "update examresultstestquestiontask set SubjectiveScore=@SubjectiveScore WHERE ExamResultsTestQuestionTaskID = @ID";

            MySqlParameter[] parms = {
                //new MySqlParameter("@BeginTime", examresultsTestQuestionTask.BeginTime),
                //new MySqlParameter("@EndTime", examresultsTestQuestionTask.EndTime),
                //new MySqlParameter("@TimeScore", examresultsTestQuestionTask.TimeScore),
                //new MySqlParameter("@SubjectiveScore", examresultsTestQuestionTask.SubjectiveScore),
                //new MySqlParameter("@ResultScore", examresultsTestQuestionTask.ResultScore),
                //new MySqlParameter("@OperationScore", examresultsTestQuestionTask.OperationScore),
                new MySqlParameter("@ID", examresultsTestQuestionTask.ID)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        public static bool UpdateExamresultsTestQuestionTaskModel(ExamresultsTestQuestionTaskModel examresultsTestQuestionTask)
        {
            string sql = "update examresultstestquestiontask set BeginTime=@BeginTime,EndTime=@EndTime,TimeScore=@TimeScore,SubjectiveScore=@SubjectiveScore,ResultScore=@ResultScore,OperationScore=@OperationScore  WHERE ExamResultsTestQuestionTaskID = @ID";
            //string sql = "update examresultstestquestiontask set SubjectiveScore=@SubjectiveScore WHERE ExamResultsTestQuestionTaskID = @ID";

            MySqlParameter[] parms = {
                //new MySqlParameter("@BeginTime", examresultsTestQuestionTask.BeginTime),
                //new MySqlParameter("@EndTime", examresultsTestQuestionTask.EndTime),
                //new MySqlParameter("@TimeScore", examresultsTestQuestionTask.TimeScore),
                //new MySqlParameter("@SubjectiveScore", examresultsTestQuestionTask.SubjectiveScore),
                //new MySqlParameter("@ResultScore", examresultsTestQuestionTask.ResultScore),
                //new MySqlParameter("@OperationScore", examresultsTestQuestionTask.OperationScore),
                new MySqlParameter("@ID", examresultsTestQuestionTask.ID)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        //modfiy by ljz 查询更新后车组好对应车辆ID
        public static String SelectTrainId(int testQuestionID)
        {
            string sqlText = "select * from Examresultstestquestiontaskstep where ID={0}";
            sqlText = string.Format(sqlText, testQuestionID);
            DataSet ds = DbHelper.ExecuteDataset(sqlText);
            var train = ds.Tables[0].Rows[0].ItemArray[22].ToString();
            return train;
            }

        public static String Seclectpar(int testQuestionID)
        {
            string sqlText = "select * from Examresultstestquestiontaskstep where ID={0}";
            sqlText = string.Format(sqlText, testQuestionID);
            DataSet ds = DbHelper.ExecuteDataset(sqlText);
            var train = ds.Tables[0].Rows[0].ItemArray[22].ToString();
            return train;
        }
        public static bool UpdateExamresultsTestQuestionTasks(List<ExamresultsTestQuestionTaskModel> taskList)
        {
            bool result = false;
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead, Timeout = new TimeSpan(0, 0, 60) }))
            {
                foreach (var task in taskList)
                {
                    result = UpdateExamresultsTestQuestionTaskModel(task);
                }
                scope.Complete();
            }
            return result;
        }

        private static ExamresultsTestQuestionTaskModel GetExamresultsTestQuestionTaskModel(DataRow dr, int mainID)
        {
            ExamresultsTestQuestionTaskModel info = new ExamresultsTestQuestionTaskModel();

            info.ID = Convert.ToInt32(dr["ID"]);
            info.ExamResultsTestQuestionID = mainID;
            info.Tooltips = dr["Tooltips"].ToString();
            info.Order = int.Parse(dr["Order"].ToString());
            if (!(dr["ResponseTime"] is DBNull))
                info.ResponseTime = int.Parse(dr["ResponseTime"].ToString());

            if (!(dr["TimeOut"] is DBNull))
                info.TimeOut = int.Parse(dr["TimeOut"].ToString());

            if (dr["IsMain"] != DBNull.Value)
                info.IsMain = dr["IsMain"].ToString() != "0";

            if (dr["TotalScore"] != DBNull.Value)
                info.TotalScore = decimal.Parse(dr["TotalScore"].ToString());
            info.Prerequisite = dr["Prerequisite"].ToString();

            if (dr["MutexTask"] != DBNull.Value)
                info.MutexTask = int.Parse(dr["MutexTask"].ToString());
            if (dr["AfterTask"] != DBNull.Value)
                info.AfterTask = int.Parse(dr["AfterTask"].ToString());
            if (dr["AfterTaskInterval"] != DBNull.Value)
                info.AfterTaskInterval = int.Parse(dr["AfterTaskInterval"].ToString());
            if (dr["TestQuestionTaskID"] != DBNull.Value)
                info.TestQuestionTaskID = int.Parse(dr["TestQuestionTaskID"].ToString());
            if (dr["UIOrder"] != DBNull.Value)
                info.UIOrder = int.Parse(dr["UIOrder"].ToString());
            if (dr["Branch"] != DBNull.Value)
                info.Branch = dr["Branch"].ToString();//主线分支
            info.ExamresultsTestQuestionTaskStepModels = ExamresultsTestQuestionTaskStepDAL.GetStepByTaskID(info.ID);
            return info;
        }

        public static void DeleteByExamResultsTestQuestionTaskID(int examResultsTestQuestionTaskID)
        {
            string sql = "DELETE FROM examresultstestquestiontask WHERE ExamResultsTestQuestionTaskID = @ID";

            MySqlParameter[] parms = {
                new MySqlParameter("@ID", examResultsTestQuestionTaskID)
            };
            DbHelper.ExecuteNonQuery(sql, parms);
        }
        #region 北京科研项目 列车相关场景动态修改 Add by lukun
        /// <summary>
        /// 动态更新试卷
        /// </summary>
        /// <param name="testQuestionID">场景试卷ID</param>
        /// <param name="orderNumber">车次号</param>
        public static void Update_ExamTestQuestionTaskModel(int testQuestionID, short orderNumber,short NewOrderNumber,short OldTrainId,short NewTrainID,string Branch,byte Up)
        {
            string sqlText = "select min(ID) as StartIndex,Max(ID) as EndIndex  from Examresultstestquestiontask" +
                                " where ExamresultsTestQuestionID={0} " + 
                                " and Branch='{1}'";
            sqlText = string.Format(sqlText, testQuestionID, Branch);
            DataSet ds = DbHelper.ExecuteDataset(sqlText);
            DataTable dt = ds.Tables[0];
            if (Up == 0x01) 
            {
                for(int i=0;i<dt.Rows.Count;i++)
                {
                    string stepSql = "update examresultstestquestiontaskstep"+
                                    " set Prerequisite = replace(Prerequisite, '{0}', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Prerequisite like '%{4}%'";

                    stepSql = string.Format(stepSql, "{@"+OldTrainId.ToString(), "{@" + NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), "{@" + OldTrainId.ToString());
                    stepSql = stepSql.Replace('@', '"');
                    DbHelper.ExecuteNonQuery(stepSql);

                    string DesSql = "update examresultstestquestiontaskstep" +
                                    " set StepDescribe = replace(StepDescribe, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepDescribe like '%[{4}]%'";

                    DesSql = string.Format(DesSql, orderNumber.ToString(),  NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(DesSql);

                    string ContentSql = "update examresultstestquestiontaskstep" +
                                    " set StepContent = replace(StepContent, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepContent like '%[{4}]%'";

                    ContentSql = string.Format(ContentSql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(ContentSql);



                    string ParaSql = "update examresultstestquestiontaskstep" +
                                    " set DevID=replace(DevID,'{0}','{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and DevType=8 and DevID like '%{4}%'";

                    ParaSql = string.Format(ParaSql, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(ParaSql);



                    string KeySql = "update examresultstestquestiontaskstep" +
                                    " set Keyword = replace(Keyword, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Keyword like '%[{4}]%'";

                    KeySql = string.Format(KeySql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(KeySql);


                    string KeysSql = "update examresultstestquestiontaskstep" +
                                   " set Keyword = replace(Keyword, '【{0}】', '{1}')" +
                                   " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Keyword like '%【{4}】%'";

                    KeysSql = string.Format(KeysSql, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(KeysSql);

                    string paramSql = "update examresultstestquestiontaskstep" +
                                    " set Parameter = replace(Parameter, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Parameter like '%{4}%'";

                    paramSql = string.Format(paramSql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(paramSql);


                    string DesSql_1 = "update examresultstestquestiontaskstep" +
                                    " set StepDescribe = replace(StepDescribe, '【{0}】', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepDescribe like '%【{4}】%'";

                    DesSql_1 = string.Format(DesSql_1, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(DesSql_1);

                    string ContentSql_1 = "update examresultstestquestiontaskstep" +
                                    " set StepContent = replace(StepContent, '【{0}】', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepContent like '%【{4}】%'";

                    ContentSql_1 = string.Format(ContentSql_1, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(ContentSql_1);
                }
            
                string sql = "update Examresultstestquestiontask testquestiontask " +
                    " set Tooltips = replace(Tooltips, '[" + orderNumber + "]', '" + NewOrderNumber + "')" +
                    " where Tooltips like '%[" + orderNumber + "]%'  and ExamresultsTestQuestionID = " + testQuestionID;
                DbHelper.ExecuteNonQuery(sql);

            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string stepSql = "update examresultstestquestiontaskstep" +
                                    " set Prerequisite = replace(Prerequisite, '{0}', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Prerequisite like '%{4}%'";

                    stepSql = string.Format(stepSql, "{@" + OldTrainId.ToString(), "{@" + NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), "{@" + OldTrainId.ToString());
                    stepSql = stepSql.Replace('@', '"');
                    DbHelper.ExecuteNonQuery(stepSql);

                    string DesSql = "update examresultstestquestiontaskstep" +
                                    " set StepDescribe = replace(StepDescribe, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepDescribe like '%[{4}]%'";

                    DesSql = string.Format(DesSql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(DesSql);

                    string ContentSql = "update examresultstestquestiontaskstep" +
                                    " set StepContent = replace(StepContent, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepContent like '%[{4}]%'";

                    ContentSql = string.Format(ContentSql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(ContentSql);

                    string ParaSql = "update examresultstestquestiontaskstep" +
                                  " set DevID=replace(DevID,'{0}','{1}')" +
                                  " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and DevType=8 and DevID like '%{4}%'";

                    ParaSql = string.Format(ParaSql, OldTrainId.ToString(),NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(ParaSql);

                    string DesSql_1 = "update examresultstestquestiontaskstep" +
                                    " set StepDescribe = replace(StepDescribe, '【{0}】', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepDescribe like '%【{4}】%'";

                    DesSql_1 = string.Format(DesSql_1, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(DesSql_1);

                    string ContentSql_1 = "update examresultstestquestiontaskstep" +
                                    " set StepContent = replace(StepContent, '【{0}】', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and StepContent like '%【{4}】%'";

                    ContentSql_1 = string.Format(ContentSql_1, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(ContentSql_1);

                    string paramSql = "update examresultstestquestiontaskstep" +
                                    " set Parameter = replace(Parameter, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Parameter like '%{4}%'";

                    paramSql = string.Format(paramSql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(paramSql);

                    string KeySql = "update examresultstestquestiontaskstep" +
                                    " set Keyword = replace(Keyword, '[{0}]', '{1}')" +
                                    " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Keyword like '%[{4}]%'";

                    KeySql = string.Format(KeySql, orderNumber.ToString(), NewOrderNumber.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), orderNumber.ToString());
                    DbHelper.ExecuteNonQuery(KeySql);


                    string KeysSql = "update examresultstestquestiontaskstep" +
                                   " set Keyword = replace(Keyword, '【{0}】', '{1}')" +
                                   " where examresultstestquestiontaskId>= {2} && examresultstestquestiontaskId <= {3} and Keyword like '%【{4}】%'";

                    KeysSql = string.Format(KeysSql, OldTrainId.ToString(), NewTrainID.ToString(), Convert.ToInt32(dt.Rows[0]["StartIndex"]), Convert.ToInt32(dt.Rows[0]["EndIndex"]), OldTrainId.ToString());
                    DbHelper.ExecuteNonQuery(KeysSql);
                }

                string sql = "update Examresultstestquestiontask testquestiontask " +
                    " set Tooltips = replace(Tooltips, '[" + orderNumber + "]', '" + NewOrderNumber + "')" +
                    " where Tooltips like '%[" + orderNumber + "]%'  and ExamresultsTestQuestionID = " + testQuestionID;
                DbHelper.ExecuteNonQuery(sql);
            }
            //北京科研更新考试扣分例外列车 add by ljz
            string sqltext2 = "select min(ID) as StartIndex,Max(ID) as EndIndex  from Examresultstestquestion" +
                                " where Id={0} ";
            sqltext2 = string.Format(sqlText, testQuestionID);
            DataSet ds2 = DbHelper.ExecuteDataset(sqlText);
            DataTable dt2 = ds2.Tables[0];
            string sql2 = "update Examresultstestquestion  set MinusPoints =replace( MinusPoints,'" + OldTrainId + "','" + NewTrainID + "');";
            DbHelper.ExecuteNonQuery(sql2);


        }
        #endregion
    }
}
